# uncompyle6 version 3.2.3
# Python bytecode 3.6 (3379)
# Decompiled from: Python 3.6.8 |Anaconda custom (64-bit)| (default, Feb 21 2019, 18:30:04) [MSC v.1916 64 bit (AMD64)]
# Embedded file name: api\handlers\frontend\targets.py
__author__ = "sanyi"
import datetime, hashlib, logging, uuid, os.path
from os.path import splitext
import sqlalchemy.exc
from dateutil.tz import tzlocal
from db.views.scans_extended import ScanExtendedViewRow
from sqlalchemy import and_ as sql_and
from sqlalchemy import func as sql_func
from sqlalchemy import or_ as sql_or
from sqlalchemy import select as sql_select
from sqlalchemy import distinct as sql_distinct
from sqlalchemy.dialects.postgresql import UUID as PG_TYPE_UUID
from api.classes.frontend.auto import *
from api.filters import Filters
from api.filters.sqlalchemy_adapter import get_sqlachemy_filter
from api.handlers.frontend import FrontendResourceHandler
from db import Connection
from db.data.access import (
    generate_target_ext_query,
    generate_target_query,
    generate_group_query,
    apply_filters,
    generate_target2group_query,
)
from db.data.users import (
    require_at_least_tech_admin_with_access_all,
    require_at_least_tech_admin,
    get_owner_id,
)
from db.tables.events import create_event, EventResources
from db.tables.groups import GroupRow
from db.tables.issue_trackers import IssueTrackerRow
from db.tables.scan_session_jobs import ScanSessionJobsTable, ScanSessionJobRow
from db.tables.scans import ScansTable, ScanRow
from db.tables.target2group import Target2GroupRow, Target2GroupTable
from db.tables.target_vulns import TargetVulnsTable, TargetVulnRow
from db.tables.targets import (
    TargetsTable,
    TargetRow,
    TargetTypes,
    TargetVerificationTypes,
)
from db.tables.targets_allowed import TargetsAllowedRow, TargetsAllowedTable
from db.tables.targets_configurations import (
    TargetConfigurationRow,
    TargetConfigurationTable,
)
from db.tables.uploads import UploadRow, UploadsTable
from db.views.targets_extended import TargetExtendedViewRow
from helpers.acusensor import get_acusensor
from helpers.licensing.features import Features
from helpers.address import (
    extract_domain_from_address,
    extract_base_url_from_address,
    get_canonical_address,
)
from server.http_responses.http_errors import BadApiRequest
from server.http_responses.http_errors import Http404, Http409, ApiErrors
from server.http_responses.http_status_codes import (
    Http204,
    Http201,
    Http200File,
    Http200,
)
from settings_provider import settings
from db.tables.excluded_hours import ExcludedHoursRow
from helpers.address.firewall import Firewall, FirewallException
from helpers.setup_types import (
    is_setup_type_on_premise_master,
    is_setup_type_aws,
    has_feature,
    SetupFeatures,
)
from helpers.encryption.text_aes_cbc import encrypt, decrypt
from helpers.encryption.exceptions import InvalidData
from helpers.encryption.text_aes_cbc import decrypt as decrypt_data
from helpers.hashing import sha256, md5
from db.tables.scan_sessions import ScanSessionRow
from db.tables.system_config import SystemConfigRow, SystemConfigTable
from helpers.licensing.usage import update_license_usage
from helpers.licensing.features import (
    OnPremiseProV12,
    OnPremiseStandardV12,
    OnPremisePocV12,
    OnPremiseTrial,
    OvsTrial,
    OvsFreeMode,
)

demo_targets = (
    "testaspnet.vulnweb.com",
    "testphp.vulnweb.com",
    "testasp.vulnweb.com",
    "testhtml5.vulnweb.com",
)
demo_targets_with_acusesnor = ("testaspnet.vulnweb.com", "testphp.vulnweb.com")
demo_targets_without_acusensor = ("testasp.vulnweb.com", "testhtml5.vulnweb.com")
logger = logging.getLogger("frontend_api_resources.Targets")
allowed_target_table = TargetsTable.alias("allowed_target_table")
target_filters = {
    "keys": {
        "criticality": {
            "value_type": "int",
            "multiple_values": True,
            "operators": ("eq", "ne", "le", "ge", "lt", "gt"),
        },
        "threat": {
            "value_type": "int",
            "multiple_values": True,
            "operators": ("eq", "ne", "le", "ge", "lt", "gt"),
        },
        "group": {"value_type": "string", "multiple_values": True, "max_len": 256},
        "target_id": {
            "value_type": "uuid4",
            "multiple_values": True,
            "operators": ("eq", "ne"),
        },
        "group_id": {"value_type": "uuid4", "multiple_values": True},
        "text_search": {
            "value_type": "string",
            "max_len": 256,
            "multiple_values": False,
            "operators": ("ilike_op",),
        },
        "last_scanned": {
            "value_type": "date",
            "multiple_values": False,
            "operators": ("eq", "lt", "gt", "le", "ge"),
        },
        "never_scanned": {"value_type": None, "multiple_values": False},
        "type": {
            "value_type": "string",
            "multiple_values": True,
            "allowed_values": ("default", "demo", "network"),
            "string_conversion": dict(default=None),
        },
    },
    "default": dict(operator="ilike_op", key="text_search"),
}


class Targets(FrontendResourceHandler):
    def __delete_target_resource(self, resource_id, resource_extension):
        try:
            if is_setup_type_aws():
                if settings.get("rq_uploads") is not None:
                    from helpers.aws.s3 import delete_file as aws_delete_file

                    destination = "%s/%s/%s" % (
                        settings["storage.targets.s3.dir"],
                        get_owner_id(self.request_user),
                        resource_id + resource_extension,
                    )
                    settings["rq_uploads"].enqueue(
                        aws_delete_file,
                        settings["storage.targets.s3.bucket"],
                        destination,
                    )
                if is_setup_type_on_premise_master():
                    pl = os.path.join(settings["storage.targets"], resource_id)
                    os.remove(pl)
        except Exception as e:
            logger.exception(
                "__delete_target_resource %s%s failed with %s",
                resource_id,
                resource_extension,
                e,
            )

    def set_continuous_scan_status(self, target_id, body, **_):
        """
        Requires target access and technical admin
        :param target_id:
        :param body:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        desired_mode = (ClsContinuousScanMode(**body)).enabled
        if desired_mode:
            self.license.require(Features.CONTINUOUS_SCANS)
        select_from, tail_filters = generate_target_query(self.request_user)
        select_from = select_from.outerjoin(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "continuous_mode",
            ),
        )
        query = (
            sql_select(
                (
                    TargetRow.target_id,
                    TargetRow.manual_intervention,
                    TargetConfigurationRow.value.label("continuous_mode"),
                    TargetRow.verification,
                    TargetRow.type,
                )
            )
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters=tail_filters)
        with Connection(self.current_shard_name, create_transactions=True) as (
            db_connection
        ):
            target = db_connection.execute(query).fetchone()
            if not target:
                return Http404()
            if desired_mode:
                if target.manual_intervention:
                    raise Http409(
                        "target requires manual intervention",
                        code=ApiErrors.MI_CONTINUOUS_NOT_MODE_SUPPORTED,
                    )
                if is_setup_type_aws():
                    if target.type == TargetTypes.DEMO:
                        raise Http409("demo target not supported")
                if target.type == TargetTypes.NETWORK_ONLY:
                    raise Http409("network only target not supported")
            if has_feature(SetupFeatures.TARGET_CHECKING_MANDATORY):
                if self.request_user["extra"].get("trusted_account"):
                    pass
                else:
                    if target.verification == TargetVerificationTypes.NONE:
                        raise Http409("target not verified")
                current_mode = bool(target.continuous_mode)
                if desired_mode == current_mode:
                    return ClsContinuousScanMode(enabled=current_mode)
                if desired_mode is False:
                    scan_id = target.continuous_mode
                    scan_extended_query = sql_select(
                        (
                            ScanExtendedViewRow.current_session_id,
                            ScanExtendedViewRow.current_status,
                        )
                    ).where(ScanExtendedViewRow.scan_id == scan_id)
                    scan_extended = db_connection.execute(
                        scan_extended_query
                    ).fetchone()
                    if scan_extended:
                        scan_session_id = scan_extended.current_session_id
                        query = (
                            ScanSessionJobsTable.update(
                                values=dict(abort_requested=True)
                            )
                        ).where(ScanSessionJobRow.scan_session_id == scan_session_id)
                        db_connection.execute(query)
                    query = (ScansTable.update(values=dict(next_run=None))).where(
                        ScanRow.scan_id == scan_id
                    )
                    db_connection.execute(query)
                    query = (
                        (TargetConfigurationTable.update(values=dict(value=None)))
                        .where(TargetConfigurationRow.target_id == target_id)
                        .where(TargetConfigurationRow.name == "continuous_mode")
                    )
                    db_connection.execute(query)
                else:
                    scan_extended_query = (
                        sql_select(
                            (
                                ScanExtendedViewRow.current_session_id,
                                ScanExtendedViewRow.current_status,
                                ScanExtendedViewRow.scan_id,
                            )
                        )
                        .where(ScanExtendedViewRow.target_id == target_id)
                        .where(ScanExtendedViewRow.continuous.is_(True))
                    )
                    scan_extended = db_connection.execute(
                        scan_extended_query
                    ).fetchone()
                    next_run = datetime.datetime.now(tzlocal())
                    next_run = (
                        datetime.datetime(
                            next_run.year,
                            next_run.month,
                            next_run.day,
                            tzinfo=tzlocal(),
                        )
                    ) + (datetime.timedelta(days=1))
                    if scan_extended:
                        if scan_extended.current_status not in (
                            "completed",
                            "aborted",
                            "failed",
                            None,
                            "scheduled",
                        ):
                            raise BadApiRequest("Try again later")
                        scan_id = scan_extended.scan_id
                        query = (
                            ScansTable.update(
                                values=dict(
                                    recurrence="DTSTART:%s\nFREQ=DAILY"
                                    % (next_run.strftime("%Y%m%d%H%M%S%z"),),
                                    next_run=next_run,
                                    schedule_disabled=False,
                                )
                            )
                        ).where(ScanRow.scan_id == scan_id)
                        db_connection.execute(query)
                    else:
                        scan_id = str(uuid.uuid4())
                        scan_data = dict(
                            scan_id=scan_id,
                            owner_id=get_owner_id(self.request_user),
                            creator_id=self.request_user["user_id"],
                            target_id=target_id,
                            profile_id=settings.get(
                                "continuous.profile.p1",
                                "11111111-1111-1111-1111-111111111114",
                            ),
                            recurrence="DTSTART:%s\nFREQ=DAILY"
                            % (next_run.strftime("%Y%m%d%H%M%S%z"),),
                            schedule_disabled=False,
                            history_limit=4,
                            schedule_start_date=next_run,
                            next_run=next_run,
                            continuous=True,
                        )
                        query = ScansTable.insert().values(**scan_data)
                        db_connection.execute(query)
                    query = (
                        (TargetConfigurationTable.update(values=dict(value=scan_id)))
                        .where(TargetConfigurationRow.target_id == target_id)
                        .where(TargetConfigurationRow.name == "continuous_mode")
                    )
                    result = db_connection.execute(query)
                    if not result.rowcount:
                        query = TargetConfigurationTable.insert().values(
                            dict(
                                target_id=target_id,
                                name="continuous_mode",
                                value=scan_id,
                            )
                        )
                        db_connection.execute(query)
        return ClsContinuousScanMode(enabled=desired_mode)

    def get_continuous_scan_status(self, target_id, **_):
        """
        Requires target access
        :param target_id:
        :param _:
        :return:
        """
        select_from, tail_filters = generate_target_query(self.request_user)
        target_query = (
            sql_select((TargetRow.target_id,))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        target_query = apply_filters(target_query, filters=tail_filters)
        config_query = (
            sql_select((TargetConfigurationRow.value,))
            .where(TargetConfigurationRow.name == "continuous_mode")
            .where(TargetConfigurationRow.target_id == target_id)
        )
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(target_query).fetchone()
            if not target:
                return Http404()
            config = db_connection.execute(config_query).fetchone()
            if config:
                current_mode = bool(config.value)
            else:
                current_mode = False
        return ClsContinuousScanMode(enabled=current_mode)

    def get_targets(self, q=None, l=None, c=None, **_):
        """
        Requires target access
        :param q:
        :param l:
        :param c:
        :param _:
        :return:
        """
        if not self.request_user:
            raise AssertionError
        filters = Filters(target_filters, q)
        limit = min(l, 100) if l is not None else 100
        c = int(c) if c is not None else 0
        cursor = c
        select_from, tail_filters = generate_target_ext_query(self.request_user)
        select_from = select_from.outerjoin(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetExtendedViewRow.target_id,
                TargetConfigurationRow.name == "target_checker",
            ),
        )
        query = sql_select(
            (
                TargetExtendedViewRow,
                TargetConfigurationRow.value.label("target_checker"),
            )
        )
        for filter_key, filter_expression in filters.data.items():
            flt = None
            if filter_key == "text_search":
                flt1 = get_sqlachemy_filter(
                    filter_expression,
                    TargetExtendedViewRow.address,
                    filters.descriptor["keys"][filter_key],
                )
                flt2 = get_sqlachemy_filter(
                    filter_expression,
                    TargetExtendedViewRow.description,
                    filters.descriptor["keys"][filter_key],
                )
                if flt1 is not None:
                    flt = sql_or(flt1, flt2)
            else:
                if filter_key == "never_scanned":
                    flt = TargetExtendedViewRow.last_scan_id.is_(None)
                else:
                    if filter_key == "type":
                        flt = get_sqlachemy_filter(
                            filter_expression,
                            TargetExtendedViewRow.type,
                            filters.descriptor["keys"][filter_key],
                        )
                    else:
                        if filter_key == "last_scanned":
                            flt = get_sqlachemy_filter(
                                filter_expression,
                                TargetExtendedViewRow.last_scan_date,
                                filters.descriptor["keys"][filter_key],
                            )
                        else:
                            if filter_key == "group_id":
                                flt = get_sqlachemy_filter(
                                    filter_expression,
                                    Target2GroupRow.group_id,
                                    filters.descriptor["keys"][filter_key],
                                )
                                if flt is not None:
                                    sf, tf = generate_target2group_query(
                                        self.request_user
                                    )
                                    sq = sql_select(
                                        (Target2GroupRow.target_id,)
                                    ).select_from(sf)
                                    sq = apply_filters(sq, tf)
                                    flt = TargetExtendedViewRow.target_id.in_(
                                        sq.where(flt)
                                    )
            if filter_key == "group":
                flt = get_sqlachemy_filter(
                    filter_expression,
                    GroupRow.name,
                    filters.descriptor["keys"][filter_key],
                )
                if flt is not None:
                    sf, tf = generate_group_query(self.request_user)
                    sq = sql_select((Target2GroupRow.target_id,)).select_from(sf)
                    sq = apply_filters(sq, tf)
                    flt = TargetExtendedViewRow.target_id.in_(sq.where(flt))
                else:
                    if filter_key == "target_id":
                        flt = get_sqlachemy_filter(
                            filter_expression,
                            TargetExtendedViewRow.target_id,
                            filters.descriptor["keys"][filter_key],
                        )
                    else:
                        if filter_key == "threat":
                            flt = get_sqlachemy_filter(
                                filter_expression,
                                TargetExtendedViewRow.threat,
                                filters.descriptor["keys"][filter_key],
                            )
                        else:
                            if filter_key == "criticality":
                                flt = get_sqlachemy_filter(
                                    filter_expression,
                                    TargetExtendedViewRow.criticality,
                                    filters.descriptor["keys"][filter_key],
                                )
                            else:
                                logger.warning("unhandled filter %s", filter_key)
                if flt is not None:
                    tail_filters.append(flt)

        query = query.select_from(select_from)
        for tail_filter in tail_filters:
            query = query.where(tail_filter)

        query = (
            query.limit(limit)
            .offset(cursor)
            .order_by(
                TargetExtendedViewRow.description,
                TargetExtendedViewRow.address,
                TargetExtendedViewRow.target_id,
            )
        )
        with Connection(self.current_shard_name) as (db_connection):
            targets = db_connection.execute(query).fetchall()
            cured_target_list = []
            if targets is not None:
                for target in targets:
                    target = dict(target)
                    target["continuous_mode"] = bool(target["continuous_mode"])
                    if target["vuln_info"] is not None:
                        severity_counts = dict(
                            info=target["vuln_info"],
                            low=target["vuln_low"],
                            medium=target["vuln_medium"],
                            high=target["vuln_high"],
                        )
                    else:
                        severity_counts = None
                    target["severity_counts"] = severity_counts
                    if is_setup_type_aws():
                        if self.request_user["extra"].get("trusted_account"):
                            target["verification"] = TargetVerificationTypes.ADMIN
                        else:
                            if target["verification"] == TargetVerificationTypes.NONE:
                                target["verification"] = "none"
                        if target["type"] is None:
                            target["type"] = "default"
                        if target["target_checker"]:
                            auth_file_name = target["target_checker"]["path"]
                            auth_file_content = target["target_checker"]["data"]
                        else:
                            auth_file_name = sha256(
                                target["target_id"], "auth_file_name"
                            )
                            auth_file_content = sha256(
                                target["target_id"], "auth_file_content"
                            )
                        target["scan_authorization"] = dict(
                            url=extract_base_url_from_address(target["address"])
                            + auth_file_name
                            + ".txt",
                            content=auth_file_content,
                        )
                    cured_target_list.append(ClsTargetItemResponse(**target))

        l = len(cured_target_list)
        if l >= limit:
            next_cursor = cursor + l
        else:
            next_cursor = None
        return ClsTargetListResponse(
            targets=cured_target_list,
            pagination=ClsPagination(previous_cursor=cursor, next_cursor=next_cursor),
        )

    def add_target(self, body, **_):
        """
        requires tech admin with target access
        :param body:
        :param _:
        :return:
        """
        require_at_least_tech_admin_with_access_all(self.request_user)
        target_data = ClsTarget(**body)
        if self.request_user["owner_id"] is None:
            owner_id = self.request_user["user_id"]
            creator_id = self.request_user["user_id"]
        else:
            owner_id = self.request_user["owner_id"]
            creator_id = self.request_user["user_id"]
        target_data.target_id = str(uuid.uuid4())
        target_data.address = target_data.address
        address = target_data.address
        domain = extract_domain_from_address(address)
        firewall = settings.get("target_firewall")
        if firewall:
            try:
                firewall.check_address(domain, do_reverse_dns=False)
            except FirewallException:
                raise Http409("address not allowed")

            if self.license.has_feature(Features.TARGET_BUSINESS_CRITICALITY):
                criticality = target_data.criticality
            else:
                criticality = 10
            is_demo_target = domain in settings.get("demo_targets", demo_targets)
        if is_demo_target:
            target_type = TargetTypes.DEMO
        else:
            target_type = target_data.get(
                "target_type", target_data.get("type", TargetTypes.DEFAULT)
            )
            if target_type == "default":
                target_type = TargetTypes.DEFAULT
            else:
                if target_type == TargetTypes.DEMO:
                    raise Http409("demo type not allowed")
            canonical_address = get_canonical_address(address)
            canonical_address_hash = md5(owner_id + canonical_address)
            t = dict(
                target_id=target_data.target_id,
                owner_id=owner_id,
                creator_id=creator_id,
                address=address,
                description=target_data.description,
                criticality=criticality,
                type=target_type,
                ca=canonical_address,
                cah=canonical_address_hash,
            )
            if target_type == TargetTypes.DEMO:
                t["verification"] = TargetVerificationTypes.DEMO
            target_query = (TargetsTable.insert().values(**t)).returning(
                TargetRow.target_id
            )
            event_query = create_event(
                "target_created",
                get_owner_id(self.request_user),
                self.request_user["user_id"],
                data=target_data.to_dict(),
                resource_type=EventResources.target,
                resource_id=target_data.target_id,
                shard=self.current_shard_name,
            )
            target_id = None
            if target_type == TargetTypes.DEMO:
                limit_name = "demo_targets"
                max_targets = self.license.max_demo_targets
            else:
                if target_type == TargetTypes.NETWORK_ONLY:
                    limit_name = "network_targets"
                    max_targets = self.license.max_network_targets
                else:
                    limit_name = "standard_targets"
                    max_targets = self.license.max_targets
                if max_targets == 0:
                    raise Http409("max %s count exceeded" % (limit_name,))
                with Connection(self.current_shard_name) as (db):
                    if max_targets is not None:
                        if target_type == TargetTypes.DEMO:
                            q = (
                                sql_select(
                                    (sql_func.count(TargetRow.target_id).label("c"),)
                                )
                                .where(TargetRow.type == target_type)
                                .where(TargetRow.owner_id == owner_id)
                                .where(TargetRow.deleted_at.is_(None))
                            )
                            c = db.execute(q).scalar()
                            if c:
                                if c >= max_targets:
                                    raise Http409(
                                        "max %s count exceeded" % (limit_name,)
                                    )
                            q = (
                                is_setup_type_aws()
                                and isinstance(self.license, (OvsTrial, OvsFreeMode))
                                and sql_select(
                                    (sql_func.count(TargetRow.target_id).label("c"),)
                                )
                                .where(TargetRow.type == target_type)
                                .where(TargetRow.owner_id == owner_id)
                                .where(TargetRow.deleted_at.is_(None))
                            )
                            c = db.execute(q).scalar()
                            if c:
                                if c >= max_targets:
                                    raise Http409(
                                        "max %s count exceeded" % (limit_name,)
                                    )
                                else:
                                    q = (
                                        sql_select((sql_func.count(TargetRow.cah),))
                                        .where(TargetRow.cah == canonical_address_hash)
                                        .where(TargetRow.deleted_at.is_(None))
                                    )
                                    c = db.execute(q).scalar()
                                    if c == 0:
                                        q = (
                                            sql_select(
                                                (
                                                    sql_func.count(
                                                        sql_distinct(TargetRow.cah)
                                                    ),
                                                )
                                            )
                                            .where(TargetRow.type == target_type)
                                            .where(TargetRow.owner_id == owner_id)
                                            .where(TargetRow.deleted_at.is_(None))
                                        )
                                        c = db.execute(q).scalar()
                                        if c:
                                            if c >= max_targets:
                                                pass
                            if is_setup_type_on_premise_master():
                                if isinstance(self.license, OnPremiseProV12):
                                    raise Http409(
                                        "max %s count exceeded" % (limit_name,)
                                    )
                                else:
                                    if isinstance(self.license, OnPremisePocV12):
                                        raise Http409(
                                            "max %s count exceeded" % (limit_name,)
                                        )
                                    else:
                                        if isinstance(
                                            self.license, OnPremiseStandardV12
                                        ):
                                            if self.license.has_feature(
                                                Features.DELETE_TARGETS
                                            ):
                                                raise Http409(
                                                    "max %s count exceeded"
                                                    % (limit_name,)
                                                )
                                            if isinstance(self.license, OnPremiseTrial):
                                                raise Http409(
                                                    "max %s count exceeded"
                                                    % (limit_name,)
                                                )
                                        else:
                                            if is_setup_type_aws():
                                                raise Http409(
                                                    "max %s count exceeded"
                                                    % (limit_name,)
                                                )
                                        if is_setup_type_aws():
                                            q = (
                                                sql_select(
                                                    (
                                                        sql_func.count(
                                                            TargetRow.target_id
                                                        ),
                                                    )
                                                )
                                                .where(TargetRow.type == target_type)
                                                .where(TargetRow.owner_id == owner_id)
                                                .where(TargetRow.deleted_at.is_(None))
                                            )
                                            c = db.execute(q).scalar()
                                            if c:
                                                if c >= max_targets * 5:
                                                    raise Http409(
                                                        "max %s count exceeded"
                                                        % (limit_name,)
                                                    )
                                    target_id = db.execute(target_query).scalar()
                                    db.execute(event_query)
                if domain in settings.get(
                    "demo_targets_with_acusesnor", demo_targets_with_acusesnor
                ):
                    with Connection(self.current_shard_name) as (db):
                        q = TargetConfigurationTable.insert().values(
                            target_id=target_id, name="sensor_secret", value="-----"
                        )
                        db.execute(q)
                        q = TargetConfigurationTable.insert().values(
                            target_id=target_id, name="sensor", value=True
                        )
                        db.execute(q)
                update_license_usage()
                return Http201(
                    location=self.base_path + "/targets/" + target_data.target_id,
                    body=target_data,
                )

    def get_excluded_paths(self, target_id, **_):
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.outerjoin(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "excluded_paths",
            ),
        )
        query = (
            sql_select(
                (TargetRow, TargetConfigurationRow.value.label("excluded_paths"))
            )
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if target is None:
                return Http404()
            if target.excluded_paths is None:
                return ClsExcludedPathList(excluded_paths=[])
            return ClsExcludedPathList(excluded_paths=target.excluded_paths)

    def update_excluded_paths(self, target_id, body, **_):
        data = ClsExcludedPathListUpdate(**body)
        add_list = data.get("add")
        if add_list:
            add_list = add_list.get("excluded_paths")
        delete_list = data.get("delete")
        if delete_list:
            delete_list = delete_list.get("excluded_paths")
        if not add_list:
            if not delete_list:
                return Http204()
            select_from, filters = generate_target_query(self.request_user)
            select_from = select_from.outerjoin(
                TargetConfigurationTable,
                sql_and(
                    TargetConfigurationRow.target_id == TargetRow.target_id,
                    TargetConfigurationRow.name == "excluded_paths",
                ),
            )
            q = (
                sql_select(
                    (TargetRow, TargetConfigurationRow.value.label("excluded_paths"))
                )
                .select_from(select_from)
                .where(TargetRow.target_id == target_id)
            )
            q = apply_filters(q, filters)
            with Connection(self.current_shard_name) as (db):
                target = db.execute(q).fetchone()
                if target is None:
                    return Http204()
                if target.excluded_paths is None:
                    excluded_paths = set()
                    excluded_path_conf_found = False
                else:
                    excluded_paths = set(target.excluded_paths)
                    excluded_path_conf_found = True
                if delete_list:
                    excluded_paths = excluded_paths.difference(delete_list)
                if add_list:
                    excluded_paths = excluded_paths.union(add_list)
                excluded_paths = list(excluded_paths)
                if is_setup_type_aws():
                    if self.request_user["license_type"] == "customer":
                        excluded_paths = excluded_paths[0:1024]
                    else:
                        if self.request_user["license_type"] == "trial":
                            excluded_paths = excluded_paths[0:100]
                    if excluded_path_conf_found:
                        q = (
                            (
                                TargetConfigurationTable.update(
                                    values=dict(value=excluded_paths)
                                )
                            )
                            .where(TargetConfigurationRow.target_id == target_id)
                            .where(TargetConfigurationRow.name == "excluded_paths")
                        )
                        db.execute(q)
                    else:
                        q = TargetConfigurationTable.insert().values(
                            value=excluded_paths,
                            name="excluded_paths",
                            target_id=target_id,
                        )
                        db.execute(q)
            return Http204()

    def get_target(self, target_id, **_):
        """
        requires target access
        :param target_id:
        :param _:
        :return:
        """
        select_from, filters = generate_target_ext_query(self.request_user)
        select_from = select_from.outerjoin(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetExtendedViewRow.target_id,
                TargetConfigurationRow.name == "target_checker",
            ),
        )
        query = sql_select(
            (
                TargetExtendedViewRow,
                TargetConfigurationRow.value.label("target_checker"),
            )
        ).select_from(select_from)
        for flt in filters:
            query = query.where(flt)

        query = query.where(TargetExtendedViewRow.target_id == target_id)
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if target is None:
                return Http404()
            target = dict(target)
            target["continuous_mode"] = bool(target["continuous_mode"])
            if target["vuln_info"] is not None:
                severity_counts = dict(
                    info=target["vuln_info"],
                    low=target["vuln_low"],
                    medium=target["vuln_medium"],
                    high=target["vuln_high"],
                )
            else:
                severity_counts = None
            target["severity_counts"] = severity_counts
            if is_setup_type_aws():
                if self.request_user["extra"].get("trusted_account"):
                    target["verification"] = TargetVerificationTypes.ADMIN
                else:
                    if target["verification"] == TargetVerificationTypes.NONE:
                        target["verification"] = "none"
                if target["type"] is None:
                    target["type"] = "default"
                if target["target_checker"]:
                    auth_file_name = target["target_checker"]["path"]
                    auth_file_content = target["target_checker"]["data"]
                else:
                    auth_file_name = sha256(target["target_id"], "auth_file_name")
                    auth_file_content = sha256(target["target_id"], "auth_file_content")
                target["scan_authorization"] = dict(
                    url=extract_base_url_from_address(target["address"])
                    + auth_file_name
                    + ".txt",
                    content=auth_file_content,
                )
            return ClsTargetItemResponse(**target)

    def update_target(self, body, target_id, **_):
        """
        Patch request
        requires tech admin with target access
        :param body:
        :param target_id:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        query = sql_select((TargetRow,)).select_from(select_from)
        query = apply_filters(query, filters)
        query = query.where(TargetRow.target_id == target_id)
        target_data = ClsTarget(
            ignored_required_keys=True, ignore_default_values=True, **body
        )
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if not target:
                return Http404()
            update = dict()
            if hasattr(target_data, "criticality"):
                if target.criticality != target_data.criticality:
                    if self.license.has_feature(Features.TARGET_BUSINESS_CRITICALITY):
                        update["criticality"] = target_data.criticality
            if hasattr(target_data, "description"):
                if target.description != target_data.description:
                    update["description"] = target_data.description
            if (
                is_setup_type_aws()
                and hasattr(target_data, "type")
                and target_data.type != target.type
            ):
                if target_data.type == "default":
                    if target.type == TargetTypes.NETWORK_ONLY:
                        max_standard_targets = self.license.max_targets
                        if max_standard_targets == 0:
                            raise Http409("max standard_targets count exceeded")
                        q = (
                            sql_select((sql_func.count(TargetRow.target_id),))
                            .where(
                                TargetRow.owner_id == get_owner_id(self.request_user)
                            )
                            .where(TargetRow.type == TargetTypes.DEFAULT)
                            .where(TargetRow.deleted_at.is_(None))
                        )
                        c = db_connection.execute(q).scalar()
                        if max_standard_targets is not None:
                            if c:
                                if c >= max_standard_targets:
                                    raise Http409("max standard_targets count exceeded")
                                update["type"] = TargetTypes.DEFAULT
                if update:
                    update_query = (TargetsTable.update(values=update)).where(
                        TargetRow.target_id == target_id
                    )
                    event_query = create_event(
                        "target_modified",
                        get_owner_id(self.request_user),
                        self.request_user["user_id"],
                        data=update,
                        resource_type=EventResources.target,
                        resource_id=target_id,
                        shard=self.current_shard_name,
                    )
                    db_connection.execute(update_query)
                    db_connection.execute(event_query)
        update_license_usage()
        return Http204()

    def remove_target(self, target_id, **_):
        """
        requires tech admin with target access
        
        OnPremiseV12 (target based license)
            - targets are deleted with delete instead mark as deleted
            if target is not used:
                we delete the target without any other action/concerns
            else:
                as of https://gitlab.acunetix.com:2223/wvs/scanner-ng/issues/798 we revert the following:
                    if count(targets) > max targets
                        - we allow deletion of targets
        
                    else
                        - we only allow deleting up to X (2) targets
                and we always let users to delete
        
        Online:
            - standard targets are deleted with marking
        
        :param target_id:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        target_query = sql_select((TargetRow.target_id, TargetRow.type)).select_from(
            select_from
        )
        for flt in filters:
            target_query = target_query.where(flt)

        target_query = target_query.where(TargetRow.target_id == target_id)
        deletion_queries = list()
        deletion_queries.append(
            TargetsAllowedTable.delete().where(
                TargetsAllowedRow.allowed_target_id == target_id
            )
        )
        deletion_queries.append(
            Target2GroupTable.delete().where(Target2GroupRow.target_id == target_id)
        )
        target_deletion_consumed = False
        target_deletion_allowance = None
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(target_query).fetchone()
            if not target:
                return Http404()
            if is_setup_type_aws():
                do_usage_check = True
                if target.type == TargetTypes.DEMO:
                    if self.request_user["license_type"] == "customer":
                        do_usage_check = False
                    else:
                        if self.request_user["license_type"] == "trial":
                            if (
                                self.request_user["license_data"]["product_code"]
                                == "OVS_FREE_MODE"
                            ):
                                do_usage_check = False
                        else:
                            if self.request_user["license_type"] == "customer":
                                if self.request_user["license_data"].get("expired"):
                                    do_usage_check = False
                        if do_usage_check:
                            q = sql_select(
                                (sql_func.count(ScanSessionRow.scan_id).label("c"),)
                            ).where(ScanSessionRow.target_id == target_id)
                            r = db_connection.execute(q).scalar()
                            if r:
                                raise Http409("target in use")
                        deletion_queries.append(
                            (
                                TargetsTable.update(
                                    values=dict(
                                        deleted_at=datetime.datetime.now(tzlocal())
                                    )
                                )
                            ).where(TargetRow.target_id == target_id)
                        )
                else:
                    if is_setup_type_on_premise_master():
                        deletion_queries.append(
                            TargetsTable.delete().where(
                                TargetRow.target_id == target_id
                            )
                        )
                    else:
                        raise Http409("unsupported setup type")
                    deletion_queries.append(
                        create_event(
                            "target_deleted",
                            get_owner_id(self.request_user),
                            self.request_user["user_id"],
                            resource_type=EventResources.target,
                            resource_id=target_id,
                            shard=self.current_shard_name,
                        )
                    )
                    for q in deletion_queries:
                        db_connection.execute(q)

        update_license_usage()
        return Http200(
            ClsTargetDeletionNotification(
                target_deletion_consumed=target_deletion_consumed,
                target_deletion_allowance=target_deletion_allowance,
            )
        )

    def check_target(self, target_id, **_):
        """
        require tech admin with target access
        :param target_id: 
        :param _: 
        :return: 
        """
        if not has_feature(SetupFeatures.TARGET_CHECKING_MANDATORY):
            raise Http404()
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        target_query = (
            sql_select((TargetRow.target_id, TargetRow.address, TargetRow.type))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        target_query = apply_filters(target_query, filters)
        with Connection(self.current_shard_name) as (db):
            target = db.execute(target_query).fetchone()
            if not target:
                raise Http404()
            if target.type != TargetTypes.DEFAULT:
                raise Http409("target type not supported")
        from helpers.target.tasks import schedule_check_target

        schedule_check_target(
            self.current_shard_name, target_id, generate_notification=False
        )
        return Http204()

    def get_check_target_task_result(self, target_id, **_):
        if not has_feature(SetupFeatures.TARGET_CHECKING_MANDATORY):
            raise Http404()
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        target_query = (
            sql_select((TargetRow.target_id, TargetRow.address))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        target_query = apply_filters(target_query, filters)
        with Connection(self.current_shard_name) as (db):
            target = db.execute(target_query).fetchone()
            if not target:
                raise Http404()
        from helpers.target.tasks import check_target_check_result

        return check_target_check_result(target_id)

    def add_allowed_host(self, target_id, body, **_):
        """
        require tech admin with target access
        :param target_id:
        :param body:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        allowed_target_id = (ClsTargetIdContainer(**body)).target_id
        if allowed_target_id == target_id:
            raise BadApiRequest("Can't add same target")
        select_from, filters = generate_target_query(self.request_user)
        query_target_ownership = sql_select(
            (TargetRow.target_id, TargetRow.address)
        ).select_from(select_from)
        query_target_ownership = apply_filters(query_target_ownership, filters)
        query_target_ownership = query_target_ownership.where(
            TargetRow.target_id.in_((target_id, allowed_target_id))
        )
        with Connection(self.current_shard_name) as (db_connection):
            targets = db_connection.execute(query_target_ownership).fetchall()
            if not targets or len(targets) != 2:
                return Http404()
            original_host = None
            new_host = None
            for target in targets:
                if target.target_id == target_id:
                    original_host = extract_domain_from_address(target.address)
                if target.target_id == allowed_target_id:
                    new_host = extract_domain_from_address(target.address)

            if original_host == new_host:
                raise BadApiRequest("Host already attached")
            allowed_domains = []
            allowed_target_id_query = sql_select(
                (TargetsAllowedRow.allowed_target_id,)
            ).where(TargetsAllowedRow.target_id == target_id)
            allowed_target_address_query = sql_select((TargetRow.address,)).where(
                TargetRow.target_id.in_(allowed_target_id_query)
            )
            for allowed_target in db_connection.execute(
                allowed_target_address_query
            ).fetchall():
                allowed_domains.append(
                    extract_domain_from_address(allowed_target.address)
                )

            if new_host in allowed_domains:
                raise BadApiRequest("Host already attached")
            max_allowed_hosts = 5
            if is_setup_type_on_premise_master():
                max_allowed_hosts = 20
            if len(allowed_domains) > max_allowed_hosts:
                raise BadApiRequest("Too many allowed targets")
            event_query = create_event(
                "target_add_allowed_host",
                get_owner_id(self.request_user),
                self.request_user["user_id"],
                data=dict(target_id=allowed_target_id, host=new_host),
                resource_type=EventResources.target,
                resource_id=target_id,
                shard=self.current_shard_name,
            )
            query = TargetsAllowedTable.insert().values(
                target_id=target_id, allowed_target_id=allowed_target_id
            )
            try:
                db_connection.execute(query)
            except sqlalchemy.exc.IntegrityError:
                pass
            else:
                db_connection.execute(event_query)

        return Http204()

    def get_allowed_hosts(self, target_id, **_):
        """
        requires target access
        :param target_id:
        :param _:
        :return:
        """
        select_from, filters = generate_target_query(self.request_user)
        filters.append(TargetRow.target_id == target_id)
        select_from_1 = select_from.join(
            TargetsAllowedTable, TargetsAllowedRow.target_id == TargetRow.target_id
        )
        select_from_1 = select_from_1.join(
            allowed_target_table,
            allowed_target_table.c.target_id == TargetsAllowedRow.allowed_target_id,
        )
        ah_query = sql_select(
            (
                allowed_target_table.c.target_id,
                allowed_target_table.c.address,
                allowed_target_table.c.description,
            )
        ).select_from(select_from_1)
        ah_query = apply_filters(ah_query, filters)
        t_query = sql_select((TargetRow.target_id,)).select_from(select_from)
        t_query = apply_filters(t_query, filters)
        hosts = []
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(t_query).fetchone()
            if not target:
                return Http404()
            for target in db_connection.execute(ah_query).fetchall():
                hosts.append(ClsAllowedHost(**dict(target)))

        return ClsAllowedHosts(hosts=hosts)

    def remove_allowed_host(self, target_id, allowed_target_id, **_):
        """
        requires tech admin with target access
        :param target_id:
        :param allowed_target_id:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        t_query = (
            sql_select((TargetRow.target_id,))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        t_query = apply_filters(t_query, filters)
        d_query = TargetsAllowedTable.delete().where(
            TargetsAllowedRow.allowed_target_id == allowed_target_id
        )
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(t_query).fetchone()
            if not target:
                return Http404()
            result = db_connection.execute(d_query)
            if result.rowcount:
                q = sql_select((TargetRow.address,)).where(
                    TargetRow.target_id == allowed_target_id
                )
                t = db_connection.execute(q).fetchone()
                event_query = create_event(
                    "target_remove_allowed_host",
                    get_owner_id(self.request_user),
                    self.request_user["user_id"],
                    data=dict(target_id=allowed_target_id, host=t.address),
                    resource_type=EventResources.target,
                    resource_id=target_id,
                    shard=self.current_shard_name,
                )
                db_connection.execute(event_query)
        return Http204()

    @staticmethod
    def download_sensor(sensor_type, sensor_secret, **_):
        """
        :param sensor_type:
        :param sensor_secret:
        :param _:
        :return:
        """
        file_name, data = get_acusensor(sensor_type, sensor_secret.encode())
        return Http200File(file_name, data)

    def reset_sensor_secret(self, target_id, body, **_):
        """
        requires tech admin with target access
        :param body:
        :param target_id:
        :param _:
        :return:
        """
        pc = ClsSensorSecretContainer(**body)
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        target_query = (
            sql_select((TargetRow.target_id, TargetRow.address))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        target_query = apply_filters(target_query, filters)
        sensor_secret = pc.get("secret")
        if not sensor_secret:
            m = hashlib.md5()
            m.update(str(uuid.uuid4()).encode())
            sensor_secret = m.hexdigest()
        encryption_password = settings.get("target_secret_salt")
        if encryption_password:
            encryption_password = sha256(target_id, encryption_password)
            se = encrypt(sensor_secret, encryption_password)
        else:
            se = sensor_secret
        password_insert_query = TargetConfigurationTable.insert().values(
            target_id=target_id, name="sensor_secret", value=se
        )
        password_update_query = (
            (TargetConfigurationTable.update(values=dict(value=se)))
            .where(TargetConfigurationRow.target_id == target_id)
            .where(TargetConfigurationRow.name == "sensor_secret")
        )
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(target_query).fetchone()
            if not target:
                return Http404()
            domain = extract_domain_from_address(target.address)
            if domain.endswith(".vulnweb.com"):
                raise Http409("Can't change test site sensor secret.")
            try:
                db_connection.execute(password_insert_query)
            except sqlalchemy.exc.IntegrityError:
                db_connection.execute(password_update_query)

            return ClsSensorSecretContainer(secret=sensor_secret)

    def get_target_configuration(self, target_id, **_):
        """
        requires tech admin with target access
        :param target_id:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.outerjoin(
            TargetConfigurationTable,
            TargetConfigurationRow.target_id == TargetRow.target_id,
        )
        query = (
            sql_select((TargetConfigurationRow.name, TargetConfigurationRow.value))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters)
        result = dict()
        found = False
        encryption_password = settings.get("target_secret_salt")
        if encryption_password:
            encryption_password = sha256(target_id, encryption_password)
        with Connection(self.current_shard_name) as (db_connection):
            for entry in db_connection.execute(query).fetchall():
                found = True
                if entry.name is None:
                    break
                result[entry.name] = entry.value

            if encryption_password:
                try:
                    result["login"]["credentials"]["password"] = decrypt(
                        result["login"]["credentials"]["password"], encryption_password
                    ).decode("utf-8")
                except (KeyError, InvalidData):
                    pass

                try:
                    result["proxy"]["password"] = decrypt(
                        result["proxy"]["password"], encryption_password
                    ).decode("utf-8")
                except (KeyError, InvalidData):
                    pass

                try:
                    result["client_certificate_password"] = decrypt(
                        result["client_certificate_password"], encryption_password
                    ).decode("utf-8")
                except (KeyError, InvalidData):
                    pass

                try:
                    result["authentication"]["password"] = decrypt(
                        result["authentication"]["password"], encryption_password
                    ).decode("utf-8")
                except (KeyError, InvalidData):
                    pass

                try:
                    result["ssh_credentials"]["password"] = decrypt(
                        result["ssh_credentials"]["password"], encryption_password
                    ).decode("utf-8")
                except (KeyError, InvalidData):
                    pass

                try:
                    result["ssh_credentials"]["ssh_key"] = decrypt(
                        result["ssh_credentials"]["ssh_key"], encryption_password
                    ).decode("utf-8")
                except (KeyError, InvalidData):
                    pass

                try:
                    result["ssh_credentials"]["key_password"] = decrypt(
                        result["ssh_credentials"]["key_password"], encryption_password
                    ).decode("utf-8")
                except (KeyError, InvalidData):
                    pass

                if "sensor_secret" not in result:
                    target_query = sql_select((TargetRow.address,)).where(
                        TargetRow.target_id == target_id
                    )
                    target = db_connection.execute(target_query).fetchone()
                    domain = extract_domain_from_address(target.address)
                    if domain in demo_targets:
                        sensor_secret = "-----"
                    else:
                        m = hashlib.md5()
                        m.update(str(uuid.uuid4()).encode())
                        sensor_secret = m.hexdigest()
                    if encryption_password:
                        se = encrypt(sensor_secret, encryption_password)
                    else:
                        se = sensor_secret
                    password_insert_query = TargetConfigurationTable.insert().values(
                        target_id=target_id, name="sensor_secret", value=se
                    )
                    db_connection.execute(password_insert_query)
                    result["sensor_secret"] = sensor_secret
                else:
                    if encryption_password:
                        pass
            try:
                result["sensor_secret"] = decrypt(
                    result["sensor_secret"], encryption_password
                ).decode("utf-8")
            except InvalidData:
                pass

        if not found:
            return Http404()
        else:
            return ClsTargetConfiguration(**result)

    def configure_target(self, target_id, configuration=None, **_):
        """
        requires tech admin with target access
        :param target_id:
        :param configuration:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)

        def is_config_entry_valid(entry):
            if entry[1] == undefined:
                return False
            elif entry[0] == "sensor_secret":
                return True
            elif entry[0] == "continuous_mode":
                return False
            else:
                return True

        configuration = ClsTargetConfiguration(
            ignored_required_keys=True, ignore_default_values=True, **configuration
        )
        valid_configurations = list(
            filter(lambda x: is_config_entry_valid(x), configuration.__dict__.items())
        )
        if not valid_configurations:
            return Http204()
        else:
            select_from, filters = generate_target_query(self.request_user)
            query = (
                sql_select((TargetRow.target_id, TargetRow.address))
                .select_from(select_from)
                .where(TargetRow.target_id == target_id)
            )
            query = apply_filters(query, filters)
            encryption_password = settings.get("target_secret_salt")
            if encryption_password:
                encryption_password = sha256(target_id, encryption_password)
            with Connection(self.current_shard_name) as (db_connection):
                target = db_connection.execute(query).fetchone()
                if target is None:
                    return Http404()
                for key, value in valid_configurations:
                    if key == "login":
                        kind = value.get("kind")
                        if kind == "sequence":
                            sf = TargetConfigurationTable.join(
                                UploadsTable,
                                UploadRow.upload_id
                                == TargetConfigurationRow.value[
                                    "upload_id"
                                ].astext.cast(PG_TYPE_UUID),
                            )
                            q = (
                                sql_select(
                                    (TargetConfigurationRow.name, UploadRow.data)
                                )
                                .select_from(sf)
                                .where(TargetConfigurationRow.target_id == target_id)
                                .where(TargetConfigurationRow.name == "login_sequence")
                            )
                            tc = db_connection.execute(q).fetchone()
                            if not tc:
                                raise Http409("login sequence not found")
                            mi = False
                            if isinstance(tc.data, dict):
                                mi = tc.data.get("mi") is True
                            q = (
                                (
                                    TargetsTable.update(
                                        values=dict(manual_intervention=mi)
                                    )
                                )
                                .where(TargetRow.target_id == target_id)
                                .where(
                                    sql_or(
                                        TargetRow.manual_intervention != mi,
                                        TargetRow.manual_intervention.is_(None),
                                    )
                                )
                            )
                            db_connection.execute(q)
                        else:
                            q = (
                                (
                                    TargetsTable.update(
                                        values=dict(manual_intervention=False)
                                    )
                                )
                                .where(TargetRow.target_id == target_id)
                                .where(
                                    sql_or(
                                        TargetRow.manual_intervention.is_(True),
                                        TargetRow.manual_intervention.is_(None),
                                    )
                                )
                            )
                            db_connection.execute(q)
                        if encryption_password:
                            pass
                        if kind == "automatic":
                            try:
                                value.credentials.password = encrypt(
                                    value.credentials.password, encryption_password
                                )
                            except KeyError:
                                pass

                        else:
                            if key == "excluded_hours_id":
                                if value:
                                    q = (
                                        sql_select(
                                            (ExcludedHoursRow.excluded_hours_id,)
                                        )
                                        .where(
                                            ExcludedHoursRow.excluded_hours_id == value
                                        )
                                        .where(
                                            ExcludedHoursRow.owner_id
                                            == get_owner_id(self.request_user)
                                        )
                                        .where(ExcludedHoursRow.deleted_at.is_(None))
                                    )
                                    if not db_connection.execute(q).fetchone():
                                        pass
                                    else:
                                        value = str(uuid.UUID(value))
                                else:
                                    q = (
                                        TargetConfigurationTable.delete()
                                        .where(
                                            TargetConfigurationRow.target_id
                                            == target_id
                                        )
                                        .where(TargetConfigurationRow.name == key)
                                    )
                                    db_connection.execute(q)
                                    continue
                            else:
                                if key == "issue_tracker_id":
                                    if value:
                                        q = (
                                            sql_select(
                                                (
                                                    sql_func.count(
                                                        IssueTrackerRow.issue_tracker_id
                                                    ),
                                                )
                                            )
                                            .where(
                                                IssueTrackerRow.issue_tracker_id
                                                == value
                                            )
                                            .where(
                                                IssueTrackerRow.owner_id
                                                == get_owner_id(self.request_user)
                                            )
                                            .where(IssueTrackerRow.deleted_at.is_(None))
                                        )
                                        if not db_connection.execute(q).scalar():
                                            continue
                                        q = (
                                            sql_select((TargetConfigurationRow.value,))
                                            .where(
                                                TargetConfigurationRow.target_id
                                                == target_id
                                            )
                                            .where(TargetConfigurationRow.name == key)
                                        )
                                        r = db_connection.execute(q).fetchone()
                                        original_issue_tracker_id = (
                                            r.value if r else None
                                        )
                                        if original_issue_tracker_id is None:
                                            if value == "":
                                                continue
                                            else:
                                                if original_issue_tracker_id:
                                                    q = (
                                                        (
                                                            TargetVulnsTable.update(
                                                                values=dict(
                                                                    issue_id=None,
                                                                    issue_tracker_id=None,
                                                                )
                                                            )
                                                        )
                                                        .where(
                                                            TargetVulnRow.target_id
                                                            == target_id
                                                        )
                                                        .where(
                                                            TargetVulnRow.issue_tracker_id
                                                            == original_issue_tracker_id
                                                        )
                                                    )
                                                    db_connection.execute(q)
                                            if value == "":
                                                q = (
                                                    TargetConfigurationTable.delete()
                                                    .where(
                                                        TargetConfigurationRow.target_id
                                                        == target_id
                                                    )
                                                    .where(
                                                        TargetConfigurationRow.name
                                                        == key
                                                    )
                                                )
                                                db_connection.execute(q)
                                                continue
                                        else:
                                            if key == "client_certificate_password":
                                                if value:
                                                    if encryption_password:
                                                        value = encrypt(
                                                            value, encryption_password
                                                        )
                                        if key == "ssh_credentials":
                                            if value.get("password"):
                                                if encryption_password:
                                                    value.password = encrypt(
                                                        value.password,
                                                        encryption_password,
                                                    )
                                            if value.get("ssh_key"):
                                                if encryption_password:
                                                    value.ssh_key = encrypt(
                                                        value.ssh_key,
                                                        encryption_password,
                                                    )
                                        if value.get("key_password"):
                                            if encryption_password:
                                                value.key_password = encrypt(
                                                    value.key_password,
                                                    encryption_password,
                                                )
                                            else:
                                                if key == "authentication":
                                                    pass
                                        if value.get("password"):
                                            if encryption_password:
                                                value.password = encrypt(
                                                    value.password, encryption_password
                                                )
                                            else:
                                                if key == "proxy":
                                                    if is_setup_type_aws():
                                                        continue
                                                        if value.get("password"):
                                                            if encryption_password:
                                                                value.password = encrypt(
                                                                    value.password,
                                                                    encryption_password,
                                                                )
                                                            else:
                                                                if key == "sensor":
                                                                    if (
                                                                        extract_domain_from_address(
                                                                            target.address
                                                                        )
                                                                        in demo_targets_without_acusensor
                                                                    ):
                                                                        value = False
                                                            update = (
                                                                (
                                                                    TargetConfigurationTable.update(
                                                                        values=dict(
                                                                            name=key,
                                                                            value=value,
                                                                        )
                                                                    )
                                                                )
                                                                .where(
                                                                    TargetConfigurationRow.target_id
                                                                    == target_id
                                                                )
                                                                .where(
                                                                    TargetConfigurationRow.name
                                                                    == key
                                                                )
                                                            )
                                                            result = db_connection.execute(
                                                                update
                                                            )
                                                            if result.rowcount == 0:
                                                                insert = TargetConfigurationTable.insert().values(
                                                                    dict(
                                                                        target_id=target_id,
                                                                        name=key,
                                                                        value=value,
                                                                    )
                                                                )
                                                                try:
                                                                    db_connection.execute(
                                                                        insert
                                                                    )
                                                                except sqlalchemy.exc.IntegrityError:
                                                                    pass

            return Http204()

    def set_login_sequence(self, target_id, body, **_):
        """
        requires tech admin with target access
        :param target_id:
        :param body:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        file_upload_descriptor = ClsFileUploadDescriptor(**body)
        select_from, filters = generate_target_query(self.request_user)
        query = (
            sql_select((TargetRow.target_id, TargetRow.type))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters)
        if 10 > file_upload_descriptor.size > 5120:
            raise BadApiRequest("Invalid size")
        v = file_upload_descriptor.name.split(".")
        if len(v) < 2 or v[-1] not in ("lsr",):
            raise BadApiRequest("Invalid name")
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if not target:
                return Http404()
            if target.type not in (TargetTypes.DEFAULT, TargetTypes.DEMO):
                raise Http409("invalid target type")
            upload_id = str(uuid.uuid4())
            new_upload = dict(
                upload_id=upload_id,
                owner_id=get_owner_id(self.request_user),
                creator_id=self.request_user["user_id"],
                expires=sql_func.current_timestamp() + (datetime.timedelta(days=7)),
                name=file_upload_descriptor.name,
                size=file_upload_descriptor.size,
                resource_id=target_id,
                actions={"ok": [("on_lsr_uploaded", {"target_id": target_id})]},
            )
            query = UploadsTable.insert().values(**new_upload)
            db_connection.execute(query)
            return ClsUploadLocationResponse(upload_url="/uploads/" + upload_id)

    def download_login_sequence(self, target_id, **_):
        result = self.get_login_sequence(target_id)
        if not result.status:
            raise Http404()
        if is_setup_type_aws():
            from helpers.aws.s3 import download_data
        try:
            data = download_data(
                settings["storage.targets.s3.bucket"],
                "targets/%s/%s.lsr"
                % (get_owner_id(self.request_user), result.upload_id),
            )
        except Exception as e:
            logging.exception("downloading login sequence failed with %s", e)
            raise Http404()

        if data:
            secret = sha256(
                get_owner_id(self.request_user) + result.upload_id,
                settings["uploads_salt"],
            )
            try:
                data = decrypt_data(data, secret, b64=False)
            except InvalidData:
                pass

            return Http200File(result.name, data)
        if is_setup_type_on_premise_master():
            try:
                data = open(
                    os.path.join(settings.get("storage.targets"), result.upload_id),
                    "rb",
                ).read()
            except Exception as e:
                logging.exception(
                    "loading login sequence for %s failed with %s", target_id, e
                )
                raise Http404()

            uploads_salt = settings.get("uploads_salt")
        if data:
            if uploads_salt:
                secret = sha256(result.upload_id, uploads_salt)
                try:
                    data = decrypt_data(data, secret, b64=False)
                except InvalidData:
                    pass

                return Http200File(result.name, data)
            raise Http404()

    def get_login_sequence(self, target_id, **_):
        """
        requires target access
        :param target_id:
        :param _:
        :return:
        """
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.join(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "login_sequence",
            ),
        )
        select_from = select_from.join(
            UploadsTable,
            UploadRow.upload_id
            == TargetConfigurationRow.value["upload_id"].astext.cast(PG_TYPE_UUID),
        )
        query = (
            sql_select((UploadRow,))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
            .where(UploadRow.deleted_at.is_(None))
        )
        query = apply_filters(query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            upload = db_connection.execute(query).fetchone()
            if not upload:
                raise Http404()
            if upload.status is not True:
                if upload.expired:
                    raise Http404()
            return ClsUploadedFile(
                upload_id=upload.upload_id,
                name=upload.name,
                size=upload.size,
                status=upload.status,
                current_size=upload.current_size,
            )

    def delete_login_sequence(self, target_id, **_):
        """
        requires tech admin with target access
        :param target_id:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.join(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "login_sequence",
            ),
        )
        query = (
            sql_select(
                (
                    TargetRow.target_id,
                    TargetConfigurationRow.value["upload_id"].label("login_sequence"),
                )
            )
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if not target or target.login_sequence is None:
                return Http204()
            target_conf_update = (
                TargetConfigurationTable.delete()
                .where(TargetConfigurationRow.target_id == target_id)
                .where(TargetConfigurationRow.name == "login_sequence")
            )
            db_connection.execute(target_conf_update)
            q = (
                (TargetsTable.update(values=dict(manual_intervention=False)))
                .where(TargetRow.target_id == target_id)
                .where(TargetRow.manual_intervention.is_(True))
            )
            db_connection.execute(q)
            target_conf_update = (
                TargetConfigurationTable.delete()
                .where(TargetConfigurationRow.target_id == target_id)
                .where(TargetConfigurationRow.name == "login_sequence")
            )
            db_connection.execute(target_conf_update)
            target_conf_update = (
                (TargetConfigurationTable.update(values=dict(value=dict(kind="none"))))
                .where(TargetConfigurationRow.target_id == target_id)
                .where(TargetConfigurationRow.name == "login")
                .where(TargetConfigurationRow.value["kind"].astext == "sequence")
            )
            db_connection.execute(target_conf_update)
            uploads_update = (
                (
                    UploadsTable.update(
                        values=dict(deleted_at=sql_func.current_timestamp())
                    )
                )
                .where(UploadRow.upload_id == target.login_sequence)
                .returning(UploadRow.name)
            )
            db_connection.execute(uploads_update).scalar()
            name = db_connection.execute(uploads_update).scalar()
            if name:
                _, ext = splitext(name)
                self._Targets__delete_target_resource(target.login_sequence, ext)
        return Http204()

    def set_client_certificate(self, target_id, body, **_):
        """
        requires tech admin with target access
        :param target_id:
        :param body:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        file_upload_descriptor = ClsFileUploadDescriptor(**body)
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.outerjoin(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "client_certificate",
            ),
        )
        query = (
            sql_select(
                (
                    TargetRow.target_id,
                    TargetRow.type,
                    TargetConfigurationRow.value.label("client_certificate"),
                )
            )
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters)
        if 10 > file_upload_descriptor.size > 5120:
            raise BadApiRequest("Invalid size")
        v = file_upload_descriptor.name.split(".")
        if len(v) < 2 or v[-1].lower() not in (
            "crt",
            "cert",
            "cer",
            "der",
            "p12",
            "pfx",
        ):
            raise BadApiRequest("Invalid name")
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if not target:
                return Http404()
            if target.type != TargetTypes.DEFAULT:
                raise Http409("invalid target type")
            upload_id = str(uuid.uuid4())
            new_upload = dict(
                upload_id=upload_id,
                owner_id=get_owner_id(self.request_user),
                creator_id=self.request_user["user_id"],
                expires=sql_func.current_timestamp() + (datetime.timedelta(days=7)),
                name=file_upload_descriptor.name,
                size=file_upload_descriptor.size,
                resource_id=target_id,
            )
            query = UploadsTable.insert().values(**new_upload)
            db_connection.execute(query)
            insert_query = TargetConfigurationTable.insert().values(
                target_id=target_id,
                name="client_certificate",
                value=dict(upload_id=str(upload_id)),
            )
            update_query = (
                (
                    TargetConfigurationTable.update(
                        values=dict(value=dict(upload_id=str(upload_id)))
                    )
                )
                .where(TargetConfigurationRow.target_id == target_id)
                .where(TargetConfigurationRow.name == "client_certificate")
            )
            try:
                db_connection.execute(insert_query)
            except sqlalchemy.exc.IntegrityError:
                db_connection.execute(update_query)

            if target.client_certificate:
                query = (
                    (
                        UploadsTable.update(
                            values=dict(deleted_at=sql_func.current_timestamp())
                        )
                    )
                    .where(
                        UploadRow.upload_id == target.client_certificate["upload_id"]
                    )
                    .returning(UploadRow.name)
                )
                name = db_connection.execute(query).scalar()
                if name:
                    _, ext = splitext(name)
                    self._Targets__delete_target_resource(
                        target.client_certificate["upload_id"], ext
                    )
            return ClsUploadLocationResponse(upload_url="/uploads/" + upload_id)

    def get_client_certificate(self, target_id, **_):
        """
        requires target access
        :param target_id:
        :param _:
        :return:
        """
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.join(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "client_certificate",
            ),
        )
        select_from = select_from.join(
            UploadsTable,
            UploadRow.upload_id
            == TargetConfigurationRow.value["upload_id"].astext.cast(PG_TYPE_UUID),
        )
        query = (
            sql_select(
                (
                    UploadRow,
                    TargetRow.target_id,
                    (sql_func.current_timestamp() > UploadRow.expires).label("expired"),
                )
            )
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
            .where(UploadRow.deleted_at.is_(None))
        )
        query = apply_filters(query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            upload = db_connection.execute(query).fetchone()
            if not upload:
                return Http404()
            if upload.status is not True:
                if upload.expired:
                    return Http404()
                return ClsUploadedFile(
                    upload_id=upload.upload_id,
                    name=upload.name,
                    size=upload.size,
                    status=upload.status,
                    current_size=upload.current_size,
                )

    def delete_client_certificate(self, target_id, **_):
        """
        requires tech admin with target access
        :param target_id:
        :param _:
        :return:
        """
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.join(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "client_certificate",
            ),
        )
        query = (
            sql_select(
                (
                    TargetRow.target_id,
                    TargetConfigurationRow.value["upload_id"].label(
                        "client_certificate"
                    ),
                )
            )
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if not target or target.client_certificate is None:
                return Http204()
            target_conf_update = (
                TargetConfigurationTable.delete()
                .where(TargetConfigurationRow.target_id == target_id)
                .where(TargetConfigurationRow.name == "client_certificate")
            )
            db_connection.execute(target_conf_update)
            uploads_update = (
                (
                    UploadsTable.update(
                        values=dict(deleted_at=sql_func.current_timestamp())
                    )
                )
                .where(UploadRow.upload_id == target.client_certificate)
                .returning(UploadRow.name)
            )
            name = db_connection.execute(uploads_update).scalar()
            if name:
                _, ext = splitext(name)
                self._Targets__delete_target_resource(target.client_certificate, ext)
        return Http204()

    def delete_imported_file(self, target_id, import_id, **_):
        require_at_least_tech_admin(self.request_user)
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.join(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "imported_files",
            ),
        )
        query = (
            sql_select(
                (
                    TargetRow.target_id,
                    TargetConfigurationRow.value.label("imported_files"),
                )
            )
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        query = apply_filters(query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(query).fetchone()
            if (
                not target
                or target.imported_files is None
                or not isinstance(target.imported_files, (list, tuple))
            ):
                return Http204()
            imported_files = target.imported_files
            if import_id not in target.imported_files:
                return Http204()
            imported_files.remove(import_id)
            target_conf_update = (
                (TargetConfigurationTable.update(values=dict(value=imported_files)))
                .where(TargetConfigurationRow.target_id == target_id)
                .where(TargetConfigurationRow.name == "imported_files")
            )
            db_connection.execute(target_conf_update)
            uploads_update = (
                (
                    UploadsTable.update(
                        values=dict(deleted_at=sql_func.current_timestamp())
                    )
                )
                .where(UploadRow.upload_id == import_id)
                .returning(UploadRow.name)
            )
            name = db_connection.execute(uploads_update).scalar()
        if name:
            _, ext = splitext(name)
            self._Targets__delete_target_resource(import_id, ext)
        return Http204()

    def upload_import_file(self, body, target_id, **_):
        """
        :param body:
        :param target_id:
        :param _:
        :return:
        """
        file_upload_descriptor = ClsFileUploadDescriptor(**body)
        require_at_least_tech_admin(self.request_user)
        if is_setup_type_aws():
            if file_upload_descriptor.size > 5242880:
                raise Http409("file too large")
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.outerjoin(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "imported_files",
            ),
        )
        target_query = (
            sql_select((TargetConfigurationRow.value, TargetRow.type))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        target_query = apply_filters(target_query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            target = db_connection.execute(target_query).fetchone()
            if not target:
                return Http404()
            if target.type != TargetTypes.DEFAULT:
                raise Http409("invalid target type")
            if target.value:
                imported_files = target.value
                if not imported_files:
                    imported_files = []
                else:
                    imported_files = []
                if len(imported_files) > 5:
                    upload_query = sql_select(
                        (
                            UploadRow,
                            (sql_func.current_timestamp() > UploadRow.expires).label(
                                "expired"
                            ),
                        )
                    ).where(UploadRow.upload_id.in_(imported_files))
                    invalid_imported_files = []
                    for upload in db_connection.execute(upload_query).fetchall():
                        if upload.status is not True:
                            if upload.expired:
                                invalid_imported_files.append(upload.upload_id)

                    imported_files = [
                        x for x in imported_files if x not in invalid_imported_files
                    ]
                if len(imported_files) > 5:
                    raise BadApiRequest("Too many imported files")
                upload_id = str(uuid.uuid4())
                new_upload = dict(
                    upload_id=upload_id,
                    owner_id=get_owner_id(self.request_user),
                    creator_id=self.request_user["user_id"],
                    expires=sql_func.current_timestamp() + (datetime.timedelta(days=7)),
                    name=file_upload_descriptor.name,
                    size=file_upload_descriptor.size,
                    resource_id=target_id,
                )
                query = UploadsTable.insert().values(**new_upload)
                db_connection.execute(query)
                imported_files.append(upload_id)
                insert_query = TargetConfigurationTable.insert().values(
                    value=imported_files, target_id=target_id, name="imported_files"
                )
                try:
                    db_connection.execute(insert_query)
                except sqlalchemy.exc.IntegrityError:
                    target_conf_update = (
                        (
                            TargetConfigurationTable.update(
                                values=dict(value=imported_files)
                            )
                        )
                        .where(TargetConfigurationRow.target_id == target_id)
                        .where(TargetConfigurationRow.name == "imported_files")
                    )
                    db_connection.execute(target_conf_update)

                return ClsUploadLocationResponse(upload_url="/uploads/" + upload_id)

    def get_imported_files(self, target_id, **_):
        """
        :param target_id:
        :param _:
        :return:
        """
        select_from, filters = generate_target_query(self.request_user)
        select_from = select_from.join(
            TargetConfigurationTable,
            sql_and(
                TargetConfigurationRow.target_id == TargetRow.target_id,
                TargetConfigurationRow.name == "imported_files",
            ),
        )
        target_query = (
            sql_select((TargetConfigurationRow.value,))
            .select_from(select_from)
            .where(TargetRow.target_id == target_id)
        )
        target_query = apply_filters(target_query, filters)
        with Connection(self.current_shard_name) as (db_connection):
            imported_files = db_connection.execute(target_query).scalar()
            uploads = []
            if not imported_files:
                return ClsUploadedFilesResponse(files=uploads)
            upload_query = sql_select(
                (
                    UploadRow,
                    (sql_func.current_timestamp() > UploadRow.expires).label("expired"),
                )
            ).where(UploadRow.upload_id.in_(imported_files))
            invalid_imported_files = []
            for upload in db_connection.execute(upload_query).fetchall():
                if upload.status is not True:
                    if upload.expired:
                        invalid_imported_files.append(upload.upload_id)
                        continue
                    uploads.append(
                        ClsUploadedFile(
                            upload_id=upload.upload_id,
                            name=upload.name,
                            size=upload.size,
                            status=upload.status,
                            current_size=upload.current_size,
                        )
                    )

            if invalid_imported_files:
                imported_files = [
                    x for x in imported_files if x not in invalid_imported_files
                ]
                target_conf_update = (
                    (TargetConfigurationTable.update(values=dict(value=imported_files)))
                    .where(TargetConfigurationRow.target_id == target_id)
                    .where(TargetConfigurationRow.name == "imported_files")
                )
                db_connection.execute(target_conf_update)
            return ClsUploadedFilesResponse(files=uploads)
